﻿/*
 * Jonathan
 * Created by: Jonathan Montiverdi
 * Date: 6/15/2012
 * License: http://jonathan.codeplex.com/license
 */
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Reflection;

namespace Jonathan.DataAccess.Sqlite
{
   /// <summary>
   /// Sqlite routines.
   /// </summary>
   public static class Sqlite
   {
      private const string PROPERTY_NAME = "PropertyName";
      private const string PROPERTY_VALUE = "PropertyValue";
      
      #region Read

      /// <summary>
      /// Reads data from the database into the given object.
      /// </summary>
      /// <param name="dbPath">Path to database to read from.</param>
      /// <param name="dataObject">Object to read data into.</param>
      /// <param name="filter">Filter to use when reading from the database.</param>
      public static void Read (string dbPath, object dataObject, string filter = "")
      {
         using (var connection = new SQLiteConnection (ConnectionString (dbPath)))
         {
            connection.Open ();
            using (var command = connection.CreateCommand ())
            {
               var dataObjType = dataObject.GetType ();
               var sqliteTable = FindSqliteTableAttribute(dataObjType);
               if (sqliteTable != null)
               {
                  command.CommandText = BuildSelectText (sqliteTable.TableName, filter);
                  using (var reader = command.ExecuteReader ())
                     ReadTable (dataObject, sqliteTable.TableType, reader);
               }
            }
            connection.Close ();
         }
      }

      /// <summary>
      /// Reads data from the database into the given object.
      /// </summary>
      /// <param name="dbPath">Path to database to read from.</param>
      /// <param name="filter">Filter to use when reading from the database.</param>
      public static IEnumerable<T> Read <T> (string dbPath, string filter = "")
      {
         using (var connection = new SQLiteConnection (ConnectionString (dbPath)))
         {
            connection.Open ();
            using (var command = connection.CreateCommand ())
            {
               var recordType = typeof (T);
               var sqliteTable = FindSqliteTableAttribute (recordType);
               if (sqliteTable != null)
               {
                  command.CommandText = BuildSelectText (sqliteTable.TableName, filter);
                  using (var reader = command.ExecuteReader ())
                  {
                     var propertyIndexDictionary = GetPropertyColumnInfo (recordType, reader);
                     
                     // Read...
                     while (reader.Read ())
                     {
                        var record = Activator.CreateInstance (recordType);
                        foreach (var kvp in propertyIndexDictionary)
                        {
                           var value = reader.GetValue (kvp.Key);
                           if (kvp.Value.PropertyType == typeof (Boolean))
                              value = value.ToString () != "0";
                           kvp.Value.SetValue (record, value, null);
                        }
                        yield return (T)record;
                     }
                  }
               }
            }
            connection.Close ();
         }
      }

      /// <summary>
      /// Reads data from the database into the given object.
      /// </summary>
      /// <param name="dbPath">Path to database to read from.</param>
      /// <param name="dataObject">Object to read data into.</param>
      /// <param name="filters">Filters to use when reading from the database
      /// (WHERE, ORDER BY, etc.. statements). This is a dictionary with the 
      /// key being the table name, and the value being the filter.</param>
      public static void ReadAll (string dbPath, object dataObject, Dictionary <string, string> filters = null)
      {
         using (var connection = new SQLiteConnection (ConnectionString (dbPath)))
         {
            connection.Open ();
            using (var command = connection.CreateCommand ())
            {
               var dataObjType = dataObject.GetType ();
               var dataObjProperties = dataObjType.GetProperties ();
               foreach (var property in dataObjProperties)
               {
                  if (property.GetCustomAttributes (typeof (SqliteIgnoreAttribute), false).Length > 0)
                     continue;
                  
                  var sqliteTable = FindSqliteTableAttribute(property.PropertyType);
                  
                  if (sqliteTable != null)
                  {
                     command.CommandText = BuildSelectText (sqliteTable.TableName, filters);
                     using (var reader = command.ExecuteReader ())
                     {
                        if (sqliteTable.TableType == SqliteTableType.Configuration)
                        {
                           var configObj = property.GetValue (dataObject, null);
                           if (configObj == null)
                           {
                              configObj = Activator.CreateInstance (property.PropertyType);
                              property.SetValue (dataObject, configObj, null);
                           }
                           ReadConfigurationTable (configObj, reader);
                        }
                        else
                        {
                           ReadRecordTable (property.GetValue (dataObject, null), reader);
                        }
                     }
                  }
               }
            }
            connection.Close ();
         }
      }
      
      /// <summary>
      /// Dumps sql data into the given object.
      /// </summary>
      /// <param name="dataObject">Object to dump data into.</param>
      /// <param name="tableType">Type of table we are dumping into/reading from.</param>
      /// <param name="reader">The sqlite reader with data.</param>
      private static void ReadTable (object dataObject, SqliteTableType tableType, SQLiteDataReader reader)
      {
         if (tableType == SqliteTableType.Configuration)
            ReadConfigurationTable (dataObject, reader);
         else
            ReadRecordTable (dataObject, reader);
      }
      
      /// <summary>
      /// Dump sql data into a configuration table object.
      /// </summary>
      /// <param name="dataObject">Object to dump data into.</param>
      /// <param name="reader">The sqlite reader with data.</param>
      private static void ReadConfigurationTable (object dataObject, SQLiteDataReader reader)
      {
         var dataProperties = dataObject.GetType ().GetProperties ();
         var nameIndex = reader.GetOrdinal (PROPERTY_NAME);
         var valueIndex = reader.GetOrdinal (PROPERTY_VALUE);
         while (reader.Read ())
         {
            var propertyName = reader.GetString (nameIndex);
            foreach (var property in dataProperties)
            {
               if (property.Name == propertyName)
               {
                  var value = reader.GetValue (valueIndex);
                  if (property.PropertyType == typeof (Boolean))
                     value = value.ToString () != "0";
                  property.SetValue (dataObject, value, null);
                  break;
               }
            }
         }
      }
      
      /// <summary>
      /// Dump sql data into a record table object.
      /// </summary>
      /// <param name="dataObject">Object to dump data into.</param>
      /// <param name="reader">The sqlite reader with data.</param>
      private static void ReadRecordTable (object dataObject, SQLiteDataReader reader)
      {
         var dataType = dataObject.GetType ();
         var recordType = dataType.GetGenericArguments () [0];
         var addMethod = dataType.GetMethod ("Add");
         var propertyIndexDictionary = GetPropertyColumnInfo (recordType, reader);
         
         // Read...
         while (reader.Read ())
         {
            var record = Activator.CreateInstance (recordType);
            foreach (var kvp in propertyIndexDictionary)
            {
               var value = reader.GetValue (kvp.Key);
               if (kvp.Value.PropertyType == typeof (Boolean))
                  value = value.ToString () != "0";
               else if (value == null)
                  value = string.Empty;
               else if (value.GetType () == typeof (System.DBNull))
                  value = string.Empty;
               kvp.Value.SetValue (record, value, null);
            }
            addMethod.Invoke (dataObject, new [] { record });
         }
      }
      
      #endregion
      
      #region Write

      /// <summary>
      /// Writes data from the given object into the database.
      /// </summary>
      /// <param name="dbPath">Path to database to write to.</param>
      /// <param name="dataObject">Object to get data from.</param>
      /// <param name="writeType">Append or Overwrite.</param>
      public static void Write (string dbPath, object dataObject, SqliteWriteType writeType = SqliteWriteType.Overwrite)
      {
         if (!File.Exists (dbPath))
            CreateEmptyDatabase (dbPath, dataObject);
         
         using (var connection = new SQLiteConnection (ConnectionString (dbPath)))
         {
            connection.Open ();
            var tableList = GetTableList (connection);
            
            var dataObjType = dataObject.GetType ();
            var sqliteTable = FindSqliteTableAttribute(dataObjType);
            using (var transaction = connection.BeginTransaction ())
            {
               if (sqliteTable != null)
               {
                  CheckTableExists (connection, sqliteTable, tableList, dataObject);
                  WriteTable (dataObject, sqliteTable, writeType, transaction);
               }
               else
               {
                  var dataObjProperties = dataObjType.GetProperties ();
                  foreach (var property in dataObjProperties)
                  {
                     if (property.GetCustomAttributes (typeof (SqliteIgnoreAttribute), false).Length > 0)
                        continue;
                     sqliteTable = FindSqliteTableAttribute(property.PropertyType);
                     if (sqliteTable != null)
                     {
                        var propertyObject = property.GetValue (dataObject, null);
                        CheckTableExists (connection, sqliteTable, tableList, propertyObject);

                        if (sqliteTable.TableType == SqliteTableType.Configuration)
                           WriteConfigurationTable (propertyObject, sqliteTable.TableName, transaction);
                        else
                           WriteRecordTable (propertyObject, sqliteTable.TableName, writeType, transaction);
                     }
                  }
               }
               transaction.Commit ();
            }
            
            connection.Close ();
         }
      }

      /// <summary>
      /// Dumps the given object into a sqlite database table.
      /// </summary>
      /// <param name="dataObject">Object to get data from.</param>
      /// <param name="sqliteTable">Table information.</param>
      /// <param name="writeType">Append or Overwrite.</param>
      /// <param name="transaction">The sqlite transaction holding the changes.</param>
      private static void WriteTable (object dataObject, SqliteTableAttribute sqliteTable,
                                      SqliteWriteType writeType, SQLiteTransaction transaction)
      {
         if (sqliteTable.TableType == SqliteTableType.Configuration)
            WriteConfigurationTable (dataObject, sqliteTable.TableName, transaction);
         else
            WriteRecordTable (dataObject, sqliteTable.TableName, writeType, transaction);
      }
      
      /// <summary>
      /// Dumps the given object into a sqlite database configuration table.
      /// </summary>
      /// <param name="dataObject">Object to get data from.</param>
      /// <param name="tableName">The name of the table to write to.</param>
      /// <param name="transaction">The sqlite transaction holding the changes.</param>
      private static void WriteConfigurationTable (object dataObject, string tableName, SQLiteTransaction transaction)
      {
         using (var command = transaction.Connection.CreateCommand ())
         {
            command.CommandText = string.Format ("DELETE FROM '{0}';", tableName);
            command.ExecuteNonQuery ();
            
            command.CommandText = string.Format ("INSERT INTO '{0}' VALUES (?, ?)", tableName);
            var nameParameter = new SQLiteParameter ();
            var valueParameter = new SQLiteParameter ();
            command.Parameters.Add (nameParameter);
            command.Parameters.Add (valueParameter);
            
            var properties = dataObject.GetType ().GetProperties ();
            foreach (var property in properties)
            {
               // Check for ignore attribute...
               if (property.GetCustomAttributes (typeof (SqliteIgnoreAttribute), false).Length > 0)
                  continue;
               
               var nameMapping = property.GetCustomAttributes (typeof (SqliteNameAttribute),
                                                               false).Cast <SqliteNameAttribute> ().FirstOrDefault ();
               var name = nameMapping == null ? property.Name : nameMapping.Name;
               
               nameParameter.Value = name;
               valueParameter.Value = property.GetValue (dataObject, null) ?? string.Empty;

               command.ExecuteNonQuery ();
            }
         }
      }
      
      /// <summary>
      /// Dumps the given object into a sqlite database record table.
      /// </summary>
      /// <param name="dataObject">Object to get data from.</param>
      /// <param name="tableName">The name of the table to write to.</param>
      /// <param name="writeType">The function the write action will perform.</param>
      /// <param name="transaction">The sqlite transaction holding the changes.</param>
      private static void WriteRecordTable (object dataObject, string tableName,
                                            SqliteWriteType writeType, SQLiteTransaction transaction)
      {
         var dataType = dataObject.GetType ();
         var recordType = dataType.GetGenericArguments () [0];
         var recordProperties = recordType.GetProperties ().ToList ();
         var enumerator = dataType.GetMethod ("GetEnumerator").Invoke (dataObject, null);
         var enumeratorType = enumerator.GetType ();
         var currentProperty = enumeratorType.GetProperty ("Current");
         var moveNextMethod = enumeratorType.GetMethod ("MoveNext");
         
         using (var command = transaction.Connection.CreateCommand ())
         {
            var fieldList = new Dictionary <string, PropertyInfo> ();
            command.CommandText = string.Format ("PRAGMA table_info({0});", tableName);
            using (var reader = command.ExecuteReader ())
            {
               while (reader.Read ())
               {
                  var fieldName = reader.GetString (1);
                  foreach (var recordProperty in recordProperties)
                  {
                     if (recordProperty.Name.Equals (fieldName))
                     {
                        fieldList.Add (fieldName, recordProperty);
                        recordProperties.Remove (recordProperty);
                        break;
                     }

                     var nameAttribute = (SqliteNameAttribute)recordProperty.GetCustomAttributes (
                        typeof (SqliteNameAttribute), false).FirstOrDefault ();
                     if (nameAttribute != null)
                     {
                        if (nameAttribute.Name.Equals (fieldName))
                        {
                           fieldList.Add (fieldName, recordProperty);
                           recordProperties.Remove (recordProperty);
                           break;
                        }
                     }
                  }
               }
            }
            
            if (writeType == SqliteWriteType.Overwrite)
            {
               command.CommandText = string.Format ("DELETE FROM '{0}';", tableName);
               command.ExecuteNonQuery ();
            }
            
            var commandText = string.Format ("INSERT INTO '{0}' (", tableName);
            var fields = string.Empty;
            var values = string.Empty;
            foreach (var field in fieldList)
            {
               command.Parameters.Add (new SQLiteParameter ());
               if (!string.IsNullOrEmpty (fields)) fields += ", ";
               fields += field.Key;
               
               if (!string.IsNullOrEmpty (values)) values += ", ";
               values += "?";
            }
            commandText += fields + ") VALUES (" + values + ");";
            command.CommandText = commandText;
            while ((bool)moveNextMethod.Invoke (enumerator, null))
            {
               int paramCounter = 0;
               var item = currentProperty.GetValue (enumerator, null);
               foreach (var field in fieldList)
               {
                  command.Parameters [paramCounter].Value = field.Value.GetValue (item, null);
                  paramCounter++;
               }
               command.ExecuteNonQuery ();
            }
         }
      }
      
      #endregion
      
      #region Create
      
      /// <summary>
      /// Creates an empty database based on the structure of the <see cref="dataObject" />.
      /// This routine assumes that the given database is either empty or non-existant.
      /// </summary>
      /// <param name="dbPath">Path to new database.</param>
      /// <param name="dataObject">Object to use as a template for the database structure.</param>
      public static void CreateEmptyDatabase (string dbPath, object dataObject)
      {
         using (var connection = new SQLiteConnection (ConnectionString (dbPath)))
         {
            connection.Open ();
            var command = connection.CreateCommand ();
            var dataObjType = dataObject.GetType ();
            var sqliteTable = FindSqliteTableAttribute(dataObjType);
            if (sqliteTable != null)
            {
               if (sqliteTable.TableType == SqliteTableType.Configuration)
                  CreateConfigurationTable (sqliteTable.TableName, command);
               else
                  CreateRecordTable (dataObject.GetType (), sqliteTable.TableName, command);
            }
            else
            {
               var dataObjProperties = dataObjType.GetProperties ();
               foreach (var property in dataObjProperties)
               {
                  if (property.GetCustomAttributes (typeof (SqliteIgnoreAttribute), false).Length > 0)
                     continue;
                  sqliteTable = FindSqliteTableAttribute (property.PropertyType);
                  if (sqliteTable != null)
                  {
                     if (sqliteTable.TableType == SqliteTableType.Configuration)
                        CreateConfigurationTable (sqliteTable.TableName, command);
                     else
                        CreateRecordTable (property.PropertyType, sqliteTable.TableName, command);
                  }
               }
            }
            
            connection.Close ();
         }
      }
      
      /// <summary>
      /// Creates a sqlite database configuration table.
      /// </summary>
      /// <param name="tableName">The name of the new table.</param>
      /// <param name="command">The sqlite command used to create the table.</param>
      private static void CreateConfigurationTable (string tableName, SQLiteCommand command)
      {
         command.CommandText = string.Format ("CREATE TABLE '{0}' ({1} TEXT, {2} TEXT);",
                                              tableName, PROPERTY_NAME, PROPERTY_VALUE);
         command.ExecuteNonQuery ();
      }
      
      /// <summary>
      /// Creates a sqlite database record table.
      /// </summary>
      /// <param name="dataObjectType">Type of object to get data from.</param>
      /// <param name="tableName">The name of the new table.</param>
      /// <param name="command">The sqlite command used to create the table.</param>
      private static void CreateRecordTable (Type dataObjectType, string tableName, SQLiteCommand command)
      {
         var genericArgs = dataObjectType.GetGenericArguments ();
         command.CommandText = BuildCreateTable (tableName, genericArgs [0].GetProperties ());
         command.ExecuteNonQuery ();
      }
      
      #endregion
      
      #region Helpers
      
      /// <summary>
      /// Checks to see if the given table exits, if not it will be created.
      /// </summary>
      /// <param name="connection">Sqlite connection.</param>
      /// <param name="sqliteTable">Sqlite table information.</param>
      /// <param name="knownTables">A list of known tables.</param>
      /// <param name="dataObject">Data object to create table for.</param>
      private static void CheckTableExists (SQLiteConnection connection, SqliteTableAttribute sqliteTable,
                                            List <string> knownTables, object dataObject)
      {
         if (!knownTables.Contains (sqliteTable.TableName))
         {
            using (var command = connection.CreateCommand ())
            {
               if (sqliteTable.TableType == SqliteTableType.Configuration)
                  CreateConfigurationTable (sqliteTable.TableName, command);
               else
                  CreateRecordTable (dataObject.GetType (), sqliteTable.TableName, command);
            }
         }
      }
      
      /// <summary>
      /// Gets a list of tables from the given connection.
      /// </summary>
      /// <param name="connection">Connection to get tables for.</param>
      /// <returns>A list of tables.</returns>
      private static List <string> GetTableList (SQLiteConnection connection)
      {
         var tableList = new List <string> ();
         using (var command = connection.CreateCommand ())
         {
            command.CommandText = "SELECT name FROM SQLITE_MASTER where type='table';";
            using (var reader = command.ExecuteReader ())
            {
               while (reader.Read ())
                  tableList.Add (reader.GetString (0));
            }
         }
         return tableList;
      }
      
      /// <summary>
      /// Builds the select text statement.
      /// </summary>
      /// <param name="tableName">Name of table to select from.</param>
      /// <param name="filter">Filter to use on select statement, if any.</param>
      /// <returns>Select text statement.</returns>
      private static string BuildSelectText (string tableName, string filter)
      {
         var text = string.Format ("SELECT * FROM {0}", tableName);
         if (!string.IsNullOrEmpty (filter))
            text += " " + filter.Trim ();
         return text + ";";
      }
      
      /// <summary>
      /// Builds the select text statement.
      /// </summary>
      /// <param name="tableName">Name of table to select from.</param>
      /// <param name="filters">Filters to use on select statement, if any.</param>
      /// <returns>Select text statement.</returns>
      private static string BuildSelectText (string tableName, Dictionary <string, string> filters)
      {
         var text = string.Format ("SELECT * FROM {0}", tableName);
         if (filters != null && filters.ContainsKey (tableName))
            text += " " + filters[tableName].Trim ();
         return text + ";";
      }
      
      /// <summary>
      /// Builds the create table sql command.
      /// </summary>
      /// <param name="tableName">Name of table.</param>
      /// <param name="properties">List of properties to use a columns in table.</param>
      /// <returns>The CREATE TABLE sql command text.</returns>
      private static string BuildCreateTable (string tableName, IEnumerable <PropertyInfo> properties)
      {
         string sqlCommand = string.Format ("CREATE TABLE '{0}' (", tableName);
         foreach (var optionProperty in properties)
         {
            // Check for ignore attribute...
            if (optionProperty.GetCustomAttributes (typeof (SqliteIgnoreAttribute), false).Length > 0)
               continue;
            
            var nameMapping = optionProperty.GetCustomAttributes (typeof (SqliteNameAttribute),
                                                                  false).Cast <SqliteNameAttribute> ().FirstOrDefault ();
            var name = nameMapping == null ? optionProperty.Name : nameMapping.Name;
            if (!sqlCommand.EndsWith ("(")) sqlCommand += ", ";
            sqlCommand += name + " ";
            sqlCommand += ConvertType (optionProperty.PropertyType);
         }
         
         sqlCommand += ");";
         return sqlCommand;
      }
      
      /// <summary>
      /// Gets the generic connection string for the given database.
      /// </summary>
      /// <param name="dbPath">Path to database you are connecting to.</param>
      /// <returns>Formatted connection string.</returns>
      public static string ConnectionString (string dbPath)
      {
         return string.Format ("Data Source={0};Version=3;", dbPath);
      }
      
      /// <summary>
      /// Converts the given type into a Sqlite type.
      /// </summary>
      /// <param name="type">C Sharp data type to convert.</param>
      /// <returns>String representation of sqlite data type.</returns>
      private static string ConvertType (Type type)
      {
         if (type  == typeof (int))
            return "INTEGER";
         if (type  == typeof (double) ||
             type  == typeof (float))
            return "REAL";
         return "TEXT";
      }
      
      /// <summary>
      /// Gets a dictionary with column indexs and property information.
      /// </summary>
      /// <param name="rowType">Type of Row data is being imported into.</param>
      /// <param name="reader">Sqlite data reader with column information.</param>
      /// <returns>Dictionary with column indexs and property information.</returns>
      private static Dictionary <int, PropertyInfo> GetPropertyColumnInfo (Type rowType, SQLiteDataReader reader)
      {
         var propertyIndexDictionary = new Dictionary <int, PropertyInfo> ();
         var typeProperties = rowType.GetProperties ();
         foreach (var typeProperty in typeProperties)
         {
            if (typeProperty.GetCustomAttributes (typeof (SqliteIgnoreAttribute), false).Length > 0)
               continue;
            
            var index = reader.GetOrdinal (typeProperty.Name);
            if (index != -1)
               propertyIndexDictionary.Add (index, typeProperty);
            else
            {
               var nameMappings = typeProperty.GetCustomAttributes (
                  typeof (SqliteNameAttribute), false).Cast <SqliteNameAttribute> ();
               foreach (var nameMapping in nameMappings)
               {
                  index = reader.GetOrdinal (nameMapping.Name);
                  if (index != -1)
                  {
                     propertyIndexDictionary.Add (index, typeProperty);
                     break;
                  }
               }
            }
         }
         return propertyIndexDictionary;
      }
      
      /// <summary>
      /// Tries to find the sqlite table attribute for the given tyep.
      /// </summary>
      /// <param name="type">Type used to search for table information.</param>
      /// <returns>SqliteTableAttribute object if found, otherwise null.</returns>
      private static SqliteTableAttribute FindSqliteTableAttribute (Type type)
      {
         var sqliteTable = (SqliteTableAttribute)type.GetCustomAttributes (typeof (SqliteTableAttribute),
                                                                           false).FirstOrDefault ();
         if (sqliteTable != null)
            return sqliteTable;
         
         if (type.IsGenericType)
         {
            var genericDefinition = type.GetGenericTypeDefinition ();
            if (genericDefinition == typeof (ObservableCollection <>) ||
                genericDefinition == typeof (IList <>) ||
                genericDefinition == typeof (ICollection <>) ||
                genericDefinition == typeof (Collection <>))
            {
               var genArgs = type.GetGenericArguments ();
               if (genArgs.Length > 0)
               {
                  sqliteTable = (SqliteTableAttribute)genArgs [0].GetCustomAttributes (typeof (SqliteTableAttribute),
                                                                                       false).FirstOrDefault ();
               }
            }
         }
         return sqliteTable;
      }
      
      #endregion
   }
}
